package cn.jit.ssm.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * 使用JDBC查询数据库，对输出所有的数据（显示行和列）
 * @author: Cecurio
 * @create: 2017-12-10 23:39
 **/
public class Jdbc {
    private static final String DRIVER_CLASSNAME = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/jit_ssm";
    private static final String USER_NAME = "root";
    private static final String PASSWORD = "root";

    static {
        try {
            Class.forName(DRIVER_CLASSNAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    public void getAccountMenu() {
        Connection connection = getConnection();
        String insertSql = "SELECT DISTINCT a.acctName,a.acctNickname, " +
            "m.moduleCode,m.moduleSuperCode,m.moduleName, " +
            "ar.roleLabel,r.roleDescription,r.roleName " +
            "FROM jit_account a " +
            "LEFT JOIN jit_account_role ar ON ar.acctName = a.acctName " +
            "LEFT JOIN jit_role r ON ar.roleLabel = r.roleLabel " +
            "LEFT JOIN jit_role_module rm ON rm.roleLabel = r.roleLabel " +
            "LEFT JOIN jit_module m ON (m.moduleCode = rm.moduleCode or m.moduleCode = rm.mosuleSuperCode) " +
            "where ar.roleLabel IS NOT NULL and a.acctSuper = 0 and a.acctName = 'shankai'";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsMetaData = null;
        try {
            pstmt = connection.prepareStatement(insertSql);
            System.out.println(insertSql);
            rs = pstmt.executeQuery();
            rsMetaData = rs.getMetaData();
            int col = rsMetaData.getColumnCount();
            rs.last();
            System.out.println(rs.getRow());
            rs.beforeFirst();
            while (rs.next()) {
                for (int i = 1; i < col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                }
                System.out.println(rs.getString(col));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void main(String[] args) {
        Jdbc jdbc = new Jdbc();
        jdbc.getAccountMenu();
    }
}
